package cn.edu.zbu.cn.edu.zbu.dao;

import cn.edu.zbu.pojo.Car;
import cn.edu.zbu.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class CarDaoImpl implements CarDao {
    @Override
    public boolean insert(Car car) {
        Connection conn=null;
        PreparedStatement pstmt=null;
        try {
            conn= JDBCUtils.getConnection();
            String sql=" insert into car values(null,?,?,?,?,?);";
            pstmt=conn.prepareStatement(sql);
            pstmt.setObject(1, car.getName());
            pstmt.setObject(2, car.getColor());
            pstmt.setObject(3, car.getSpeed());
            pstmt.setObject(4, car.getBirthday());
            pstmt.setObject(5, car.getPrice());
            int i = pstmt.executeUpdate();//增、删、改，查（executequery）
            if (i > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //收尾工作，关闭打开的资源
            JDBCUtils.close(null, pstmt, conn);
        }
        return false;
    }

    @Override
    public boolean delete(int id) {
        Connection conn=null;
        PreparedStatement pstmt=null;
        try {
            conn= JDBCUtils.getConnection();
            String sql=" delete  from car where id=?";
            pstmt=conn.prepareStatement(sql);
            pstmt.setObject(1, id);
            int i = pstmt.executeUpdate();//增、删、改，查（executequery）
            if (i > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //收尾工作，关闭打开的资源
            JDBCUtils.close(null, pstmt, conn);
        }
        return false;
    }

    @Override
    public boolean update(Car car) {
        Connection conn=null;
        PreparedStatement pstmt=null;
        try {
            conn= JDBCUtils.getConnection();
            String sql="update car set name=?,color=?,speed=?,birthday=?,price=? where id=?";
            pstmt=conn.prepareStatement(sql);
            pstmt.setObject(1, car.getName());
            pstmt.setObject(2, car.getColor());
            pstmt.setObject(3, car.getSpeed());
            pstmt.setObject(4, car.getBirthday());
            pstmt.setObject(5, car.getPrice());
            pstmt.setObject(6, car.getId());

            int i = pstmt.executeUpdate();//增、删、改，查（executequery）
            if (i > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //收尾工作，关闭打开的资源
            JDBCUtils.close(null, pstmt, conn);
        }
        return false;
    }

    @Override
    public List<Car> findAll() {
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        List<Car> cars=new ArrayList<>();
        try {
            conn= JDBCUtils.getConnection();
            String sql="SELECT * from car ";
            pstmt=conn.prepareStatement(sql);
            rs= pstmt.executeQuery();
            ////////////////////////////// rs.next能使记录指针向下移动一行，返回值是下一行存在为true，不存在为false
            while(rs.next()){
                //可以根据字段序号来获取，也可以根据字体名称。
               int id=rs.getInt("id");
               String name=rs.getString("name");
               String color=rs.getString("color");
               String speed=rs.getString("speed");
               Date birthday=rs.getDate("birthday");
               String price=rs.getString("price");
               Car car=new Car(id,name,color,speed,birthday,price);
               cars.add(car);
            }
            return  cars;
            ////////////////////////////
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(null,pstmt,conn);
        }
        return cars;
    }

    @Override
    public Car findById(int id) {
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try {
            conn= JDBCUtils.getConnection();
            String sql="SELECT * from car where id=? ";
            pstmt=conn.prepareStatement(sql);
            pstmt.setObject(1,id);
            rs= pstmt.executeQuery();
            ////////////////////////////// rs.next能使记录指针向下移动一行，返回值是下一行存在为true，不存在为false
            if(rs.next()){
                //可以根据字段序号来获取，也可以根据字体名称。
                String name=rs.getString("name");
                String color=rs.getString("color");
                String speed=rs.getString("speed");
                Date birthday=rs.getDate("birthday");
                String price=rs.getString("price");
                Car car=new Car(id,name,color,speed,birthday,price);
               return car;
            }
            return  null;
            ////////////////////////////
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(null,pstmt,conn);
        }
        return null;
    }

    @Override
    public int findTotal() {
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try {
            conn= JDBCUtils.getConnection();
            String sql="SELECT count(*) from car ";
            pstmt=conn.prepareStatement(sql);
            rs= pstmt.executeQuery();
            ////////////////////////////// rs.next能使记录指针向下移动一行，返回值是下一行存在为true，不存在为false
            if(rs.next()){
                return  rs.getInt(1);
            }
            ////////////////////////////
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(null,pstmt,conn);
        }
        return 0;
    }

    @Override
    public List<Car> findByCurrentpage(int currentPage, int rows) {

        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        List<Car> cars=new ArrayList<>();
        try {
            conn= JDBCUtils.getConnection();
            String sql="SELECT * from car limit ?,? ";
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,(currentPage-1)*rows);
            pstmt.setInt(2,rows);
            rs= pstmt.executeQuery();
            ////////////////////////////// rs.next能使记录指针向下移动一行，返回值是下一行存在为true，不存在为false
            while(rs.next()){
                //可以根据字段序号来获取，也可以根据字体名称。
                int id=rs.getInt("id");
                String name=rs.getString("name");
                String color=rs.getString("color");
                String speed=rs.getString("speed");
                Date birthday=rs.getDate("birthday");
                String price=rs.getString("price");
                Car car=new Car(id,name,color,speed,birthday,price);
                cars.add(car);
            }
            return  cars;
            ////////////////////////////
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(null,pstmt,conn);
        }
        return cars;
    }
}
